if __name__ == '__main__':
    import pandas as pd

    # 更新天数。sheet1，sheet2

    df1 = pd.read_excel("D:\\中断输入\\评价体系\\用户数.xlsx", sheet_name='Sheet1')
    df2 = pd.read_excel("D:\\中断输入\\评价体系\\用户数.xlsx", sheet_name='Sheet2')
    order = pd.read_excel("D:\\中断输入\\评价体系\\用户数.xlsx", sheet_name='Sheet3')
    time = df1[['分公司/月份', '时长']]
    df1 = df1[['分公司/月份', '中断用户数']]
    people_branch1 = df1[df1['分公司/月份'] == '中区']['中断用户数'].values[0]
    people_branch2 = df1[df1['分公司/月份'] == '丰台']['中断用户数'].values[0]
    print(people_branch1)
    print(people_branch2)
    df1.loc[len(df1)] = ['城二', people_branch1 + people_branch2]
    people_branch1 = df1[df1['分公司/月份'] == '石门']['中断用户数'].values[0]
    people_branch2 = df1[df1['分公司/月份'] == '海淀']['中断用户数'].values[0]
    df1.loc[len(df1)] = ['城三', people_branch1 + people_branch2]
    df1['分公司/月份'] = df1['分公司/月份'].replace('朝阳', '城一')
    df1['日均'] = df1['中断用户数'] / 28
    print(df1)
    df3 = df2.merge(df1, left_on='分公司', right_on='分公司/月份')
    df3['压降'] = (df3['日均拍照值'] - df3['日均']) / df3['日均拍照值']
    df3 = df3[['分公司', '压降']]
    df3.loc[len(df3)] = ['中区', df3[df3['分公司'] == '城二']['压降'].values[0]]
    df3.loc[len(df3)] = ['丰台', df3[df3['分公司'] == '城二']['压降'].values[0]]
    df3.loc[len(df3)] = ['石门', df3[df3['分公司'] == '城三']['压降'].values[0]]
    df3.loc[len(df3)] = ['海淀', df3[df3['分公司'] == '城三']['压降'].values[0]]
    df3['分公司'] = df3['分公司'].replace('城一', '朝阳')
    df3 = order.merge(df3, on='分公司').merge(time, left_on='分公司', right_on='分公司/月份')
    df3.to_excel("D:\\中断输入\\评价体系\\压降.xlsx")
